** Data preparing for summary statistics

/*
Table 1: Summary Statistics for the HS Six-Digit Product-Level Matching and Firm-Level Matching in Textile/Apparel Trade from Mexico to the United States

Table A4: Summary Statistics

Table A5: Summary Statistics for Product-Level Matching: Regression Samples

Table A6: Summary Statistics for Firm-Level Matching: Regression Samples

Table A9: Product-Level Characteristics in 2004

Table A10: Importer-Product Level Characteristics in 2004

Table A11: Exporter-Product Level Characteristics in 2004
*/


* Make data on products that have always more than 2 exporters and 2 importers for all the years for 2004-2007
use "${output_data}matched_exp_imp_hs6_junedecember_maquila_restrict.dta", clear
drop if newclid2==.
keep rfc year newclid2 hs6 value_exporter_importer
collapse (sum) value_exporter_importer, by (rfc year newclid2 hs6)
bys year hs6 rfc: gen exporter=(_n==1)
bys year hs6 newclid2: gen importer=(_n==1)
sort hs6
bysort hs6 year: egen n_exp=sum(exporter)
bysort hs6 year: egen n_imp=sum(importer)
sort hs6 year
drop if hs6==hs6[_n-1] & year==year[_n-1]
keep hs6 year n_exp n_imp
keep if year<=2007
* Does hs6 data exist from 2004-07?
gen a=1
bysort hs6: egen aa=sum(a) // It exists if aa==4
** Keep the products that have always more than 2 exporters and 2 importers for all the years for 2004-2007
gen rest=1 if n_exp >=2 & n_imp >=2
replace rest=0 if rest==.
bysort hs6: egen rest_min = min(rest)
bysort hs6: egen rest_always2 = max(rest_min)
replace rest_always=0 if aa!=4
keep year hs6 n_exp n_imp rest_always
save "${temp_data}nexp_nimp_hs6.dta", replace


** For Table 1 and Table A4
* Input: 
* - ${output_data}matched_exp_imp_hs6_junedecember_maquila_restrict
* - ${rawdata}nexp_nimp_hs6.dta
*
* Output: 
* - ${output_data}summarystats_product_04050607.dta
* - ${output_data}summarystats_firm_04050607.dta

********** product_level ********************
* Mean and median number of exporters and importers at each hs6 product
use "${output_data}matched_exp_imp_hs6_junedecember_maquila_restrict.dta", clear
keep if year<=2007
merge m:1 hs6 year using "${temp_data}nexp_nimp_hs6.dta"
* Keep the products that have always more than 2 exporters and 2 importers for all the years for 2004-2007
keep if rest_always==1 
* Drop one product (HS570210) where the number of importers unreasonably fluctuates
drop if hs6=="570210"
collapse (sum) value_exporter_importer, by (rfc newclid2 hs6 year)
drop if value_exporter_importer==0
gen nexp=1
bys rfc hs6 year: gen id=_n
replace nexp=0 if id!=1
gen nimp=1
bys newclid2 hs6 year: gen id2=_n
replace nimp=0 if id2!=1
collapse (sum) nexp nimp, by (hs6 year)
collapse (mean) mean_nexp=nexp mean_nimp=nimp (median) median_nexp=nexp median_nimp=nimp, by(year)
label var mean_nexp "Number of Exporters (mean)"
label var mean_nimp "Number of Importers (mean)"
label var median_nexp "Number of Exporters (median)"
label var median_nimp "Number of Importers (median)"
order year mean_nexp median_nexp mean_nimp median_nimp
save "${temp_data}nexp_nimp_product.dta", replace

* Mean and median number of buyers (importers) at exporter-product level
use "${output_data}matched_exp_imp_hs6_junedecember_maquila_restrict.dta", clear
keep if year<=2007
merge m:1 hs6 year using "${temp_data}nexp_nimp_hs6.dta"
keep if rest_always==1
drop if hs6=="570210"
collapse (sum) value_exporter_importer, by(rfc newclid2 hs6 year)
drop if value_exporter_importer==0
bys rfc hs6 year: gen nimp_per_exporter=_N
collapse nimp_per_exporter (sum) totexport=value_exporter_importer, by(rfc hs6 year)
collapse (mean) mean_nimp_per_exporter=nimp_per_exporter (median) median_nimp_per_exporter=nimp_per_exporter, by(year)
label var mean_nimp_per_exporter "Number of Importers Buying from an Exporter (mean)"
label var median_nimp_per_exporter "Number of Importers Buying from an Exporter (median)"
save "${temp_data}nimp_per_exp_product.dta", replace

* Value share of the main buyers (importers) at exporter-product level
use "${output_data}matched_exp_imp_hs6_junedecember_maquila_restrict.dta", clear
keep if year<=2007
merge m:1 hs6 year using "${temp_data}nexp_nimp_hs6.dta"
keep if rest_always==1
drop if hs6=="570210"
collapse (sum) value_exporter_importer, by(rfc newclid2 hs6 year)
drop if value_exporter_importer==0
bys rfc hs6 year: gen nimp_per_exporter=_N
collapse  nimp_per_exporter (max) maxexport=value_exporter_importer (sum) totexport=value_exporter_importer, by(rfc hs6 year)
gen main_importershare_per_exporter=maxexport/totexport
keep if nimp_per_exporter>1
collapse main_importershare_per_exporter, by(year)
label var main_importershare_per_exporter "Value Share of Main Importer"
save "${temp_data}main_importershare_per_exporter_product.dta", replace

* Mean and median number of sellers (exporters) at importer-product level
use "${output_data}matched_exp_imp_hs6_junedecember_maquila_restrict.dta", clear
keep if year<=2007
merge m:1 hs6 year using "${temp_data}nexp_nimp_hs6.dta"
keep if rest_always==1
drop if hs6=="570210"
collapse (sum) value_exporter_importer, by(rfc newclid2 hs6 year)
drop if value_exporter_importer ==0
bys newclid2 hs6 year: gen nexp_per_importer=_N
collapse nexp_per_importer (sum) totexport=value_exporter_importer, by(newclid2 hs6 year)
collapse (mean) meannexp_per_importer=nexp_per_importer (median) mediannexp_per_importer=nexp_per_importer, by(year)
label var meannexp_per_importer "Number of Exporters Selling to an Importer (mean)"
label var mediannexp_per_importer "Number of Exporters Selling to an Importer (median)"
save "${temp_data}nexp_per_imp_product.dta", replace

* Value share of the main seller (exporters) at importer-product level
use "${output_data}matched_exp_imp_hs6_junedecember_maquila_restrict.dta", clear
keep if year<=2007
merge m:1 hs6 year using "${temp_data}nexp_nimp_hs6.dta"
keep if rest_always==1
drop if hs6=="570210"
collapse (sum) value_exporter_importer, by (rfc newclid2 hs6 year)
drop if value_exporter_importer ==0
bys newclid2 hs6 year: gen  nexp_per_importer=_N
collapse  nexp_per_importer (max) maximport=value_exporter_importer  (sum) totimport=value_exporter_importer , by (newclid2 hs6 year)
keep if nexp_per_importer>1
gen main_exportershare_per_importer=maximport/totimport
collapse main_exportershare_per_importer, by (year)
label var main_exportershare_per_importer "Value Share of Main Exporter"
save "${temp_data}main_exportershare_per_importer_product.dta", replace

/** Combining all the files **/
use "${temp_data}nexp_nimp_product.dta", replace
merge 1:1 year using "${temp_data}nexp_per_imp_product.dta"
drop _merge
merge 1:1 year using "${temp_data}nimp_per_exp_product.dta"
drop _merge
merge 1:1 year using "${temp_data}main_exportershare_per_importer_product.dta"
drop _merge
merge 1:1 year using "${temp_data}main_importershare_per_exporter_product.dta"
drop _merge
save "${output_data}summarystats_product_04050607.dta", replace


********** firm_level ********************
* Number of exporters and importers at firm level
use "${output_data}matched_exp_imp_hs6_junedecember_maquila_restrict.dta", clear
keep if year<=2007
merge m:1 hs6 year using "${temp_data}nexp_nimp_hs6.dta"
keep if rest_always==1
drop if hs6=="570210"
collapse (sum) value_exporter_importer, by(rfc newclid2 year)
drop if value_exporter_importer==0
gen nexp=1
bys rfc year: gen id=_n
replace nexp=0 if id!=1
gen nimp=1
bys newclid2 year: gen id2=_n
replace nimp=0 if id2!=1
collapse (sum) nexp nimp, by (year)
label var nexp "Number of Exporters"
label var nimp "Number of Importers"
save "${temp_data}nexp_nimp_firm.dta", replace

* Mean and median number of buyers (importers) buying from an exporter at firm level 
use "${output_data}matched_exp_imp_hs6_junedecember_maquila_restrict", clear
keep if year<=2007
merge m:1 hs6 year using "${temp_data}nexp_nimp_hs6.dta"
keep if rest_always==1
drop if hs6=="570210"
collapse (sum) value_exporter_importer, by (rfc newclid2 year)
drop if value_exporter_importer==0
bys rfc year: gen nimp_per_exporter=_N
collapse nimp_per_exporter (sum) totexport=value_exporter_importer, by (rfc year)
collapse (mean) meannimp_per_exporter=nimp_per_exporter (median) mediannimp_per_exporter=nimp_per_exporter, by (year)
label var meannimp_per_exporter "Number of Importers Buying from an Exporter (mean)"
label var mediannimp_per_exporter "Number of Importers Buying from an Exporter (median)"
save "${temp_data}nimp_per_exp_firm.dta", replace

* Value share of the main buyers (importers) at firm level
use "${output_data}matched_exp_imp_hs6_junedecember_maquila_restrict", clear
keep if year<=2007
merge m:1 hs6 year using "${temp_data}nexp_nimp_hs6.dta"
keep if rest_always==1
drop if hs6=="570210"
collapse (sum) value_exporter_importer, by(rfc newclid2 year)
drop if value_exporter_importer ==0
bys rfc year: gen nimp_per_exporter=_N
collapse  nimp_per_exporter (max) maxexport=value_exporter_importer (sum) totexport=value_exporter_importer, by (rfc year)
gen mainimportershare_perexporter=maxexport/totexport
keep if nimp_per_exporter>1
collapse mainimportershare_perexporter, by (year)
label var mainimportershare_perexporter "Value Share of Main Importer"
save "${temp_data}main_importershare_per_exporter_firm.dta", replace

* Mean and median number of sellers (exporters) selling to an importer at firm level
use "${output_data}matched_exp_imp_hs6_junedecember_maquila_restrict", clear
keep if year<=2007
merge m:1 hs6 year using "${temp_data}nexp_nimp_hs6.dta"
keep if rest_always==1
drop if hs6=="570210"
collapse (sum) value_exporter_importer, by (rfc newclid2 year)
drop if value_exporter_importer ==0
bys newclid2 year: gen nexp_per_importer=_N
collapse nexp_per_importer (sum) totexport=value_exporter_importer , by (newclid2 year)
collapse (mean) meannexp_per_importer=nexp_per_importer (median) mediannexp_per_importer=nexp_per_importer , by (year)
label var meannexp_per_importer "Number of Exporters Selling to an Importer (mean)"
label var mediannexp_per_importer "Number of Exporters Selling to an Importer (median)"
save "${temp_data}nexp_per_imp_firm.dta", replace

* Value share of the main sellers (exporters) at firm level
use "${output_data}matched_exp_imp_hs6_junedecember_maquila_restrict", clear
keep if year<=2007
merge m:1 hs6 year using "${temp_data}nexp_nimp_hs6.dta"
keep if rest_always==1
drop if hs6=="570210"
collapse (sum) value_exporter_importer, by (rfc newclid2 year)
drop if value_exporter_importer ==0
bys newclid2 year: gen  nexp_per_importer=_N
collapse  nexp_per_importer (max) maximport=value_exporter_importer  (sum) totimport=value_exporter_importer , by (newclid2 year)
keep if nexp_per_importer>1
gen mainexportershare_perimporter=maximport/totimport
collapse mainexportershare_perimporter, by (year)
label var mainexportershare_perimporter "Value Share of Main Exporter"
save "${temp_data}main_exportershare_per_importer_firm.dta", replace

/** Combining all the files **/
use "${temp_data}nexp_nimp_firm.dta", replace
merge 1:1 year using "${temp_data}nexp_per_imp_firm.dta"
drop _merge
merge 1:1 year using "${temp_data}nimp_per_exp_firm.dta"
drop _merge
merge 1:1 year using  "${temp_data}main_exportershare_per_importer_firm.dta"
drop _merge
merge 1:1 year using  "${temp_data}main_importershare_per_exporter_firm.dta"
drop _merge
save "${output_data}summarystats_firm_04050607.dta", replace


** For Table A5 and A6
* Input: 
* - ${output_data}matched_exp_imp_hs6_junedecember_maquila_restrict
* - ${output_data}US_for_analysis_04_07_hs6_restrict.dta
* - ${output_data}mexico_for_analysis_04_07_hs6_restrict.dta
* - ${output_data}importer_rank.dta
* - ${output_data}exporter_rank.dta
*
* Output: 
* - ${output_data}summarystats_regsample_product_04050607.dta
* - ${output_data}summarystats_regsample_firm_04050607.dta

* Identify regression sample, US
use "${output_data}US_for_analysis_04_07_hs6_restrict.dta", clear
* Set iq_mex_bind80 as a binding measure
rename iq_mex_bind80 Binding
merge m:1 hs6_importer_id using "${output_data}importer_rank.dta"
gen rrank_own=(importer_own_rank-1)/(n_importer_2004-1)
gen rrank_own_Binding=rrank_own*Binding
gen i_down_mv_rank_d0704=1 if e_mv_rank_d0704>0
replace  i_down_mv_rank_d0704=0 if e_mv_rank_d0704<=0
replace  i_down_mv_rank_d0704=. if e_mv_rank_d0704==.
label variable i_e_mv_rank_d0704 "US Partner Upgrading"
label variable i_down_mv_rank_d0704 "US Partner Downgrading"
eststo clear
eststo: xi:reg i_e_mv_rank_d0704 Binding i.hs2, cluster(hs6)
keep if e(sample)
keep newclid2 hs6
gen usregression=1
save "${temp_data}usregsample.dta", replace

* Identify regression sample, Mexico
use "${output_data}mexico_for_analysis_04_07_hs6_restrict.dta", clear
* Set iq_mex_bind80 as a binding measure
rename iq_mex_bind80 Binding
merge m:1 hs6_exporter_id using "${output_data}exporter_rank.dta"
gen rrank_own=(exporter_own_rank-1)/(n_exporter_2004-1)
gen rrank_own_Binding=rrank_own*Binding
gen i_up_mv_rank_d0704=1 if mv_rank_d0704<0
replace  i_up_mv_rank_d0704=0 if mv_rank_d0704>=0
replace  i_up_mv_rank_d0704=. if mv_rank_d0704==.
label variable i_up_mv_rank_d0704 "Mex Partner Upgrading"
label variable i_mv_rank_d0704 "Mex Partner Downgrading"
eststo: xi:reg i_up_mv_rank_d0704 Binding i.hs2, cluster(hs6)
keep if e(sample)
gen rfc=substr(hs6_exporter_id, 8, .)
order hs6 rfc hs6_exporter_id
keep rfc hs6
gen mexregression=1
save "${temp_data}mexregsample.dta", replace

* Make dataset restricting to regression sample
use "${output_data}matched_exp_imp_hs6_junedecember_maquila_restrict", clear
merge m:1 rfc hs6 using  "${temp_data}mexregsample.dta"
drop _merge
merge m:1 newclid2 hs6 using  "${temp_data}usregsample.dta"
drop _merge
preserve
keep if mexregression==1 
save "${temp_data}matched_exp_imp_hs6_junedecember_restrict_mxreg.dta", replace
restore
keep if usregression==1 
save "${temp_data}matched_exp_imp_hs6_junedecember_restrict_usreg.dta", replace


********** product_level ********************
* Number of importers
use "${temp_data}matched_exp_imp_hs6_junedecember_restrict_mxreg.dta", clear
keep if year<=2007
collapse (sum) value_exporter_importer, by(newclid2 hs6 year)
drop if value_exporter_importer==0
bys hs6 year: gen nimporter_mexregsample=_N
collapse nimporter_mexregsample, by (year)
label var nimporter_mexregsample "Number of Importers"
save "${temp_data}nimp_mexreg.dta", replace

* Number of importers per exporter-product
use "${temp_data}matched_exp_imp_hs6_junedecember_restrict_mxreg.dta", clear
keep if year<=2007
collapse (sum) value_exporter_importer, by (rfc newclid2 hs6 year)
drop if value_exporter_importer ==0
bys rfc hs6 year: gen nimp_per_exporter=_N
collapse nimp_per_exporter (sum) totexport=value_exporter_importer, by(rfc hs6 year)
collapse (mean) meannimp_per_exporter=nimp_per_exporter (median) mediannimp_per_exporter=nimp_per_exporter, by(year)
label var meannimp_per_exporter "Number of Importers Buying from an Exporter (mean)"
label var mediannimp_per_exporter "Number of Importers Buying from an Exporter (median)"
save "${temp_data}nimp_per_exp_product_reg.dta", replace

* Value share of the main buyers (importers) 
use "${temp_data}matched_exp_imp_hs6_junedecember_restrict_mxreg.dta", clear
keep if year<=2007
collapse (sum) value_exporter_importer, by(rfc newclid2 hs6 year)
drop if value_exporter_importer ==0
bys rfc hs6 year: gen nimp_per_exporter=_N
collapse  nimp_per_exporter (max) maxexport=value_exporter_importer (sum) totexport=value_exporter_importer, by(rfc hs6 year)
gen mainimportershare_perexporter=maxexport/totexport
keep if nimp_per_exporter>1
collapse mainimportershare_perexporter, by(year)
label var mainimportershare_perexporter "Value Share of the Main Importer"
save "${temp_data}main_importershare_per_exporter_product_reg.dta", replace

* Number of exporters
use "${temp_data}matched_exp_imp_hs6_junedecember_restrict_usreg.dta", clear
keep if year<=2007
collapse (sum) value_exporter_importer, by (rfc hs6 year)
drop if value_exporter_importer ==0
bys hs6 year: gen nexporter_usregsample=_N
collapse nexporter_usregsample , by (year)
save "${temp_data}nexp_usreg.dta", replace

* Mean and median number of sellers (exporters) 
use "${temp_data}matched_exp_imp_hs6_junedecember_restrict_usreg.dta", clear
keep if year<=2007
collapse (sum) value_exporter_importer, by (rfc newclid2 hs6 year)
drop if value_exporter_importer ==0
bys newclid2 hs6 year: gen nexp_per_importer=_N
collapse nexp_per_importer (sum) totexport=value_exporter_importer , by (newclid2 hs6 year)
collapse (mean) meannexp_per_importer=nexp_per_importer (median) mediannexp_per_importer=nexp_per_importer , by(year)
label var meannexp_per_importer "Number of Exporters Selling to an Importer (mean)"
label var mediannexp_per_importer "Number of Exporters Selling to an Importer (median)"
save "${temp_data}nexp_per_imp_product_reg.dta", replace

* Value share of the main seller (exporters)
use "${temp_data}matched_exp_imp_hs6_junedecember_restrict_usreg.dta", clear
keep if year<=2007
collapse (sum) value_exporter_importer, by (rfc newclid2 hs6 year)
drop if value_exporter_importer ==0
bys newclid2 hs6 year: gen  nexp_per_importer=_N
collapse nexp_per_importer (max) maximport=value_exporter_importer (sum) totimport=value_exporter_importer, by(newclid2 hs6 year)
keep if nexp_per_importer>1
gen mainexportershare_perimporter=maximport/totimport
collapse mainexportershare_perimporter, by (year)
label var mainexportershare_perimporter "Value Share of the Main Exporter"
save "${temp_data}main_exportershare_per_importer_product_reg.dta", replace

/** Combining all the files **/
use "${temp_data}nexp_usreg.dta", replace
merge 1:1 year using "${temp_data}nimp_mexreg.dta"
drop _merge
merge 1:1 year using "${temp_data}nexp_per_imp_product_reg.dta"
drop _merge
merge 1:1 year using "${temp_data}nimp_per_exp_product_reg.dta"
drop _merge
merge 1:1 year using "${temp_data}main_exportershare_per_importer_product_reg.dta"
drop _merge
merge 1:1 year using "${temp_data}main_importershare_per_exporter_product_reg.dta"
drop _merge
save "${output_data}summarystats_regsample_product_04050607.dta", replace


********** firm_level ********************
* Number of importers
use "${temp_data}matched_exp_imp_hs6_junedecember_restrict_mxreg.dta", clear
keep if year<=2007
collapse (sum) value_exporter_importer, by(newclid2 year)
drop if value_exporter_importer ==0
bys year: gen nimporter_mexregsample=_N
collapse nimporter_mexregsample, by(year)
save "${temp_data}nimp_mexreg_firm.dta", replace

* Number of importers per exporter-product
use "${temp_data}matched_exp_imp_hs6_junedecember_restrict_mxreg.dta", clear
keep if year<=2007
collapse (sum) value_exporter_importer, by(rfc newclid2 year)
drop if value_exporter_importer ==0
bys rfc year: gen nimp_per_exporter=_N
collapse nimp_per_exporter (sum) totexport=value_exporter_importer, by (rfc year)
collapse (mean) meannimp_per_exporter=nimp_per_exporter (median) mediannimp_per_exporter=nimp_per_exporter, by(year)
save "${temp_data}nimp_per_exp_product_reg_firm.dta", replace

* Value share of the main Buyers (importers) at Exporter-Product level
use "${temp_data}matched_exp_imp_hs6_junedecember_restrict_mxreg.dta", clear
keep if year<=2007
collapse (sum) value_exporter_importer, by(rfc newclid2 year)
drop if value_exporter_importer==0
bys rfc year: gen nimp_per_exporter=_N
collapse  nimp_per_exporter (max) maxexport=value_exporter_importer (sum) totexport=value_exporter_importer, by(rfc year)
gen mainimportershare_perexporter=maxexport/totexport
keep if nimp_per_exporter>1
collapse mainimportershare_perexporter, by(year)
save "${temp_data}main_importershare_per_exporter_product_reg_firm.dta", replace

* Number of exporters
use "${temp_data}matched_exp_imp_hs6_junedecember_restrict_usreg.dta", clear
keep if year<=2007
collapse (sum) value_exporter_importer, by(rfc year)
drop if value_exporter_importer ==0
bys year: gen nexporter_usregsample=_N
collapse nexporter_usregsample, by(year)
save "${temp_data}nexp_usreg_firm.dta", replace

* Mean and median number of sellers (exporters) 
use "${temp_data}matched_exp_imp_hs6_junedecember_restrict_usreg.dta", clear
keep if year<=2007
collapse (sum) value_exporter_importer, by(rfc newclid2 year)
drop if value_exporter_importer ==0
bys newclid2 year: gen nexp_per_importer=_N
collapse nexp_per_importer (sum) totexport=value_exporter_importer, by(newclid2 year)
collapse (mean) meannexp_per_importer=nexp_per_importer (median) mediannexp_per_importer=nexp_per_importer, by(year)
save "${temp_data}nexp_per_imp_product_reg_firm.dta", replace

* Value share of the main seller (exporters) 
use "${temp_data}matched_exp_imp_hs6_junedecember_restrict_usreg.dta", clear
keep if year<=2007
collapse (sum) value_exporter_importer, by(rfc newclid2 year)
drop if value_exporter_importer ==0
bys newclid2 year: gen  nexp_per_importer=_N
collapse nexp_per_importer (max) maximport=value_exporter_importer (sum) totimport=value_exporter_importer , by(newclid2 year)
keep if nexp_per_importer>1
gen mainexportershare_perimporter=maximport/totimport
collapse mainexportershare_perimporter, by(year)
save "${temp_data}main_exportershare_per_importer_product_reg_firm.dta", replace

/** Combining all the files **/
use "${temp_data}nexp_usreg_firm.dta", replace
merge 1:1 year using "${temp_data}nimp_mexreg_firm.dta"
drop _merge
merge 1:1 year using "${temp_data}nexp_per_imp_product_reg_firm.dta"
drop _merge
merge 1:1 year using "${temp_data}nimp_per_exp_product_reg_firm.dta"
drop _merge
merge 1:1 year using "${temp_data}main_exportershare_per_importer_product_reg_firm.dta"
drop _merge
merge 1:1 year using "${temp_data}main_importershare_per_exporter_product_reg_firm.dta"
drop _merge
save "${output_data}summarystats_regsample_firm_04050607.dta", replace



** For Table A9: 
* Input: 
* - ${output_data}matched_exp_imp_hs6_junedecember_maquila_restrict
* - ${rawdata}description_hs6_type.dta
* - ${output_data}quota_mex_weight_hs6.dta

* Output: 
* - ${output_data}product_level_characteristics.dta

* Number of exporters by hs6
use "${output_data}matched_exp_imp_hs6_junedecember_maquila_restrict", clear
keep if year==2004
drop if newclid2==.
gen nexp2004=1
collapse nexp2004, by(rfc hs6)
collapse (sum) nexp2004, by(hs6)
save "${temp_data}nexp2004.dta", replace

* Number of importers by hs6
use "${output_data}matched_exp_imp_hs6_junedecember_maquila_restrict", clear
keep if year==2004
drop if newclid2==.
gen nimp2004=1
collapse nimp2004, by(newclid2 hs6)
collapse (sum) nimp2004, by(hs6)
save "${temp_data}nimp2004.dta", replace

use "${output_data}matched_exp_imp_hs6_junedecember_maquila_restrict", clear
keep if year==2004
bys rfc hs6 year: gen nimp=_N
gsort rfc hs6 year -value_exporter_importer
bys rfc hs6 year: gen order_imp=_n
gen main_importer=(order_imp==1)
bys newclid2 hs6 year: gen nexp=_N
gsort newclid2 hs6 year -value_exporter_importer
bys newclid2 hs6 year: gen order_exp=_n
gen main_exporter=(order_exp==1)
gen onetoone=(nexp==1 & nimp==1)
gsort rfc hs6 year -value_exporter_importer
gen mainmain=(order_imp==1 & order_exp==1)
collapse (sum) value_exporter_importer, by(year hs6 mainmain)
bys year hs6: egen totvalue=sum(value)
keep if mainmain==1
keep if year==2004
gen mainmainratio=value/totvalue
keep hs6 mainmainratio
save "${temp_data}mainmain2004.dta", replace

use "${output_data}matched_exp_imp_hs6_junedecember_maquila_restrict", clear
keep if year==2004
* Set iq_mex_bind80 as a binding measure
merge m:1 hs6 using "${output_data}quota_mex_weight_hs6.dta"
drop if _merge==2
rename iq_mex_bind80 Binding
collapse (sum) value_exporter_importer, by (hs6 hs2 Binding)
merge 1:1 hs6 using "${rawdata}description_hs6_type.dta"
drop if _merge==2
drop _merge
merge 1:1 hs6 using "${temp_data}mainmain2004.dta"
drop _merge
merge 1:1 hs6 using "${temp_data}nexp2004.dta"
drop _merge
merge 1:1 hs6 using "${temp_data}nimp2004.dta"
drop _merge
drop if Binding==.

gen nimp_per_nexp=nimp2004/nexp2004
gen lvalue=log(value_exporter_importer)
replace men=0 if hs2!="61"&hs2!="62"
replace woman=0 if hs2!="61"&hs2!="62"
replace cotton=0 if hs2!="61"&hs2!="62"
replace cotton=1 if hs2=="52"
replace Wool=0 if hs2!="61"&hs2!="62"
replace Wool=1 if hs2=="51"
replace manmade=0 if hs2!="61"&hs2!="62"
replace manmade=1 if hs2=="54" | hs2=="55"
label variable nexp2004 "Number of Exporters"
label variable nimp2004 "Number of Importers"
label variable value "Total Trade Volume"
label variable lvalue "Log Total Trade Volume"
label variable nimp_per_nexp "N Importers/N Exporters"
label variable Binding "Binding"
save "${output_data}product_level_characteristics.dta", replace



** For Table A10
* Input: 
* - ${output_data}matched_exp_imp_hs6_junedecember_maquila_restrict
* - ${output_data}final_state.dta
* - ${output_data}quota_mex_weight_hs6.dta
* Output: 
* - ${output_data}import_product_level_characteristics.dta

use "${output_data}matched_exp_imp_hs6_junedecember_maquila_restrict", clear
merge m:1 hs6 using "${output_data}quota_mex_weight_hs6.dta"
drop if _merge==2
rename iq_mex_bind80 Binding
collapse maquilavalue (sum) value_exporter_importer, by(rfc hs6 hs2 year newclid2 Binding Manufacturing Retail Wholesale)
bys rfc hs6 year: egen firmhs6export=sum(value)
gen importershare=value/firmhs6export
gen maquilavalue2=maquilavalue*importershare
merge m:1 rfc using "${output_data}final_state.dta"
drop if _merge==2
drop _merge
gen north=0
replace north=1 if estado==2 | estado==5 | estado==8 | estado==19 | estado==26 | estado==28
keep if year==2004
bys newclid2 hs6: egen firmhs6import=sum(value)
bys newclid2 hs6: egen firmhs6maquilaimport=sum(maquilavalue2)
bys newclid2 hs6: gen nseller=_N
gsort newclid2 hs6 -value
by newclid2 hs6: gen main=(_n==1)
gen mainshare=value/firmhs6import
replace north=0 if main!=1
collapse firmhs6import firmhs6maquilaimport nseller (max) mainshare north, by(newclid2 hs6 hs2 Binding Manufacturing Retail Wholesale)
gen maquilashare=firmhs6maquilaimport/firmhs6import
bys newclid2 : gen nproduct=_N
bys newclid2: egen firmimport=sum(firmhs6import)

gen intermed_only=0
replace intermed_only=1 if ((Retail>0 & Retail!=.) | (Wholesale>0 & Wholesale!=.)) & (Manuf==0 & Manuf!=.)
replace intermed_only=. if Retail==. & Wholesale==.  & Manuf==.
replace mainshare=. if nseller==1
gen lfirmhs6import=log(firmhs6import)

foreach x in intermed_only lfirmhs6import nseller maquilashare mainshare north{
gen d`x' = 0
replace d`x'=1 if `x'!=.
replace d`x'=0 if d`x'!=1
}
drop if newclid2==.
drop if dintermed!=. & Binding==.
replace Binding=0 if Binding==.
label variable Binding "Binding"
save "${output_data}import_product_level_characteristics.dta", replace



** For Table A11
* Input: 
* - ${output_data}matched_exp_imp_hs6_junedecember_maquila_restrict
* - ${output_data}final_state.dta
* - ${output_data}quota_mex_weight_hs6.dta
* Output: 
* - ${output_data}export_product_level_characteristics.dta

use "${output_data}matched_exp_imp_hs6_junedecember_maquila_restrict", clear
merge m:1 hs6 using "${output_data}quota_mex_weight_hs6.dta"
drop if _merge==2
rename iq_mex_bind80 Binding
collapse (sum) value_exporter_importer maquilavalue, by(rfc hs6 hs2 year newclid2 Binding Manufacturing Retail Wholesale)
keep if year==2004
merge m:1 rfc using ${output_data}final_state.dta
drop if _merge==2
bys rfc hs6: egen firmhs6export=sum(value)
bys rfc hs6: gen nbuyer=_N
gsort rfc hs6 -value
by rfc hs6: gen main=(_n==1)
gen mainshare=value/firmhs6export
drop _merge

gen intermed_only=0
replace intermed_only=1 if ((Retail>0 & Retail!=.) | (Wholesale>0 & Wholesale!=.)) & (Manuf==0 & Manuf!=.)
replace intermed_only=. if Retail==. & Wholesale==. & Manuf==.
replace intermed_only=. if main!=1
collapse firmhs6export maquilavalue nbuyer (max) mainshare intermed_only, by(rfc estado hs6 hs2 Binding)
gen maquilashare=maquilavalue/firmhs6export
bys rfc: egen firmexport=sum(firmhs6export)
gen north=0
replace north=1 if estado==2 | estado==5 | estado==8 | estado==19 | estado==26 | estado==28
replace mainshare=. if nbuyer==1
gen lfirmhs6export=log(firmhs6export)

foreach x in maquilashare north lfirmhs6export nbuyer mainshare intermed_only{
gen d`x' = 0
replace d`x'=1 if `x'!=.
replace d`x'=0 if d`x'!=1
}
drop if rfc==""
replace Binding=0 if Binding==.
label variable Binding "Binding"
label var maquilashare "Value share of maquila"
label var north "Northern State Dummies"
label var lfirmhs6export "Log Firm-HS6 Export"
label var nbuyer "Number of Buyers"
label var mainshare "Value share of main partner"
label var intermed_only "Intermediary Only"
save "${output_data}export_product_level_characteristics.dta", replace
